CREATE PROCEDURE [dbo].[eod_fundamentals_api]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- 1. Delete all "empty" rows
	DELETE	raw.eod_fundamentals 
	WHERE	fun_ind_desc = ''
			AND fun_sct_desc = ''
			AND fun_shares = 0
			AND fun_market_cap = 0
			AND fun_pe = 0
			AND fun_eps = 0
			AND fun_div_yield = 0
			AND fun_ebitda = 0
			AND fun_peg = 0
			AND fun_pts = 0
			AND fun_ptb = 0
			AND fun_yield = 0;

	-- 2. Delete duplicate rows
	WITH source AS (
		SELECT	  fun_sym_cd
				, fun_exc_cd
				, fun_dt
				, rn = ROW_NUMBER() OVER (PARTITION BY    fun_sym_cd
														, fun_exc_cd
														, fun_dt
										  ORDER BY		fun_id DESC)
		FROM	raw.eod_fundamentals
	)
	DELETE FROM source WHERE rn > 1;

	-- 3. Load "Industries"
	MERGE INTO	fin.eod_industries WITH (HOLDLOCK) AS target
	USING		(SELECT		DISTINCT fun_ind_desc
				 FROM		raw.eod_fundamentals)	AS source
	ON			target.ind_desc = source.fun_ind_desc
	WHEN NOT MATCHED BY target THEN
		INSERT	( ind_desc
				, ind_cd )
		VALUES	( fun_ind_desc
				, '' );

	-- 4. Load "Sectors"
	MERGE INTO	fin.eod_sectors WITH (HOLDLOCK) AS target
	USING		(SELECT		DISTINCT fun_sct_desc
				 FROM		raw.eod_fundamentals)	AS source
	ON			target.sct_desc = source.fun_sct_desc
	WHEN NOT MATCHED BY target THEN
		INSERT	( sct_desc
				, sct_cd )
		VALUES	( fun_sct_desc
				, '' );

	-- 5. Load Fundamentals
	MERGE INTO	fin.eod_fundamentals WITH (HOLDLOCK) AS target
	USING		(SELECT	  sym_id 
						, ind_id
						, sct_id
						, fun_dt
						, fun_shares
						, fun_market_cap
						, fun_pe
						, fun_eps
						, fun_div_yield
						, fun_ebitda
						, fun_peg
						, fun_pts
						, fun_ptb
						, fun_yield
				FROM	raw.eod_fundamentals
						INNER JOIN	fin.eod_exchanges 
						ON			(fun_exc_cd = exc_cd)
						INNER JOIN	fin.eod_symbols 
						ON			(fun_sym_cd = sym_cd 
									AND sym_exc_id = exc_id)
						INNER JOIN	fin.eod_industries 
						ON			(fun_ind_desc = ind_desc)
						INNER JOIN	fin.eod_sectors 
						ON			(fun_sct_desc = sct_desc)) AS source
	ON				target.fun_sym_id = source.sym_id
				AND	target.fun_dt = source.fun_dt
	WHEN MATCHED THEN
		UPDATE SET		  target.fun_ind_id = source.ind_id
						, target.fun_sct_id = source.sct_id
						, target.fun_shares = source.fun_shares
						, target.fun_market_cap = source.fun_market_cap
						, target.fun_pe = source.fun_pe
						, target.fun_eps = source.fun_eps
						, target.fun_div_yield = source.fun_div_yield
						, target.fun_ebitda = source.fun_ebitda
						, target.fun_peg = source.fun_peg
						, target.fun_pts = source.fun_pts
						, target.fun_ptb = source.fun_ptb
						, target.fun_yield = source.fun_yield
	WHEN NOT MATCHED BY target THEN
		INSERT	( fun_sym_id
				, fun_ind_id
				, fun_sct_id
				, fun_dt
				, fun_shares
				, fun_market_cap
				, fun_pe
				, fun_eps
				, fun_div_yield
				, fun_ebitda
				, fun_peg
				, fun_pts
				, fun_ptb
				, fun_yield)
		VALUES	(sym_id 
				, ind_id
				, sct_id
				, fun_dt
				, fun_shares
				, fun_market_cap
				, fun_pe
				, fun_eps
				, fun_div_yield
				, fun_ebitda
				, fun_peg
				, fun_pts
				, fun_ptb
				, fun_yield);
END